// 查询记录
const { getWhereStr } = require("./_utils");
const { N, needParam, splitModChar, splitAliasChar, consoleLog, typeOf } = require("../../utils");
const { getSomeFieldNames, getStandFieldNames } = require("../../compile/mysql/_utils");
const { toStandardSqlField } = require("../../compile/mysql/_utils");
const { standTables } = require("../../../../../app/tables-data");
const { getTablesSelectSql } = require("./_utils/tablesSelect");

/**
 * 获取JOIN表的字符串
 * @param {string} mainKey 主表的主键，例：user.id
 * @param {object} joinTableMap 要连接表的映射，例： { order: "user_id" }
 * @notice 左连接返回左表中的所有行以及右表中满足连接条件的行；右连接返回右表中的所有行以及左表中满足连接条件的行（简记：左链接优先满足左表，右链接优先满足右表）
 */
function getJoinTableStr(joinTableMap) {
  if (!joinTableMap) return "";
  const joinTableNames = Object.keys(joinTableMap);
  if (!joinTableNames.length) return "";
  // modName 带修饰符的表单名称
  return joinTableNames
    .map(modName => {
      const [tableNameStr, joinType = "LEFT"] = modName.split(splitModChar);
      const [tName, aName = ""] = tableNameStr.split(splitAliasChar);
      const equalStr = joinTableMap[modName];
      return `${joinType.toUpperCase()} JOIN ${tName} ${aName} ON ${equalStr}`;
    })
    .join(N);
}

/**
 * 获取orderBy的sql语句
 * @param {string[]} order orderBy的规则，例：[ "update_time|ASC", "create_time|DESC" ]，默认按DESC（降序）排序
 * @returns {string}
 */
function getOrderByStr(order, allFieldNames = []) {
  if (!order?.length) return "";
  const t = typeOf(order);
  if (t === "Array") {
    return order
      .map(key => {
        const [prop, mod = "DESC"] = key.split("|");
        if (allFieldNames?.length && !allFieldNames.includes(prop)) return "";
        return `${prop} ${mod.toUpperCase()}`;
      })
      .filter(it => !!it)
      .join(", ");
  } else {
    throw new Error(`暂未处理此类型：${t}`);
  }
}

/**
 * 获取单、多表查询的sql语句
 * @param {object} table 表名称或多表数据 例：'user'或[{ user: ["id", "name"]}, {order: ["user_id", "order_id", "amount"] }]
 * @param {string|boolean|array|object} where where规则
 * @notice 在上述映射结构中，第一个表为主表，其他为从表。主表第一个字段为主键，从表第一个字段为join主键，且这两个键都是必须的
 */
// 使用示例：
// 单表查询
// selectTable({user: undefined}, { user_name: "李四", age|>=: "20" });
// selectTable({user: ['*']}, { user_name: ["李四", "张三"] });
// 模糊查询
// selectTable({user: ['*']}, { user_name|like: "%四%"}); // 名字中含有四
// 正则表达式查询
// selectTable({user: ['*']}, { user_name|regexp: "^李.+" }); // 姓李的
// 取反查询
// selectTable({user: ['*']}, [{ "user_name|not": ["李四", "张三"], "age|!=": "24" }, { user_name: "王五" }]);
// 多表查询：order: 默认 LEFT 连接，log 指定为INNER连接，不会查出log表的字段，会查出price表的所有字段（除关联的主键外）
// selectTable({ user: ["id", "user_name", "nickname", "age"], order: ["user_id", "price", "amount"], "log|inner": ["user_id"], "price": ["user_id", "*"] }, { "user.age|>": "20", "order.price|>=": "20" });
// ASC 升序（从小到大），DESC 降序（从大到小）。
// order = [ "update_time|ASC", "create_time|DESC" ] 默认DESC排序
// limits = [0, 20]
exports.selectTable = function (table = needParam(), { where, order, limits, group, having }) {
  // user: [`${tableName}.create_id=user.id`, "user_name@create_name"]
  // console.log(table, "table-------------table");
  // const t = typeOf(table);
  // let mainFieldNames = "";
  // if (["String", "Object"].includes(table)) throw new Error(`暂未处理此种类型：${t}`);
  // let sql = "";
  // let isSingle = false;
  // let nChar = "";
  // let mainName = "";
  // let tableNames = [];
  // const joinTableMap = {};
  // if (t === "Array") {

  const isSingle = typeof table === "string" || table.length === 1; // 是否是单表查询
  const nChar = isSingle ? " " : N;
  const mainName = typeof table === "string" ? table : Object.keys(table[0])[0].split(splitAliasChar)[0];
  const mainFieldNames = getStandFieldNames(getStandFieldNames(["*"], mainName), mainName);
  const tableNames = isSingle ? [] : table.map(it => Object.keys(it)[0].split(splitAliasChar)[0]);
  sql = getTablesSelectSql(table);

  // } else {
  //   const tableMap = t === "String" ? { [table]: getStandFieldNames(["*"], table) } : table;
  //   tableNames = Object.keys(tableMap);
  //   const [mainNameStr] = tableNames;
  //   const [mName] = mainNameStr.split(splitAliasChar);
  //   mainName = mName;
  //   // 主表第一个字段必须是主键，且不会有任何修饰符
  //   mainFieldNames = t === "String" ? tableMap[mainName] : getStandFieldNames(tableMap[mainName], mainName); // ?? getSomeFieldNames(mainName);
  //   isSingle = tableNames.length === 1; // 是否是单表查询
  //   nChar = isSingle ? " " : N;
  //   // modName 带修饰符的表单名称
  //   const fieldsStr = tableNames
  //     .map((modName, ind) => {
  //       const [tNameStr] = modName.split(splitModChar);
  //       const [tName, aName] = tNameStr.split(splitAliasChar); // tName真实表名 aName真实表名的别名
  //       const { sqlFields } = standTables[tName];
  //       const isMain = ind === 0;
  //       // 考虑要转换日期格式，所以需要把['*']拆开成多个字段
  //       let fieldNames = isMain ? mainFieldNames : getStandFieldNames(tableMap[modName] ?? ["*"]);
  //       // let fieldNames = isMain ? mainFieldNames : tableMap[modName];
  //       // 后面的join表，要去除掉第一个关联主键
  //       if (isMain) {
  //         if (!isSingle) {
  //           if (!fieldNames[0]) throw new Error(`主表${tName}主键不能为空`);
  //           const { isPrimaryKey, name: fieldName } = toStandardSqlField(fieldNames[0]);
  //           if (!isPrimaryKey) throw new Error(`主表${tName}第一个键必须为主键，当前为：${fieldName}`);
  //         }
  //       } else {
  //         if (!fieldNames?.length) throw new Error(`从表${tName}主键不能为空`);
  //         const [equalStr, ...restFields] = fieldNames;
  //         joinTableMap[modName] = equalStr;
  //         // 考虑要转换日期格式，所以需要把['*']拆开成多个字段
  //         // 如果指定['*']，则为查询子表的其余所有字段（除关联主键外）。
  //         const subMainKey = equalStr
  //           .split("=")
  //           .map(it => it.trim())[1]
  //           .split(".")[1];
  //         fieldNames = getStandFieldNames(restFields, tName, [subMainKey]);
  //       }
  //       return (
  //         fieldNames
  //           .map((fieldMod, i) => {
  //             const t = typeOf(fieldMod);
  //             if (t === "String") {
  //               // 从表的第一个字段为 user.create_id=user.id 形式，故不参与处理
  //               // if (!isMain && i === 0) return "";
  //               let [field, aliasName] = fieldMod.split(splitAliasChar); // splitAliasChar 后面的是别名
  //               const fieldInfo = sqlFields.find(it => it.name === field);
  //               if (!fieldInfo) throw new Error(`${tName}表中不存在字段：${field}`);
  //               const { type } = fieldInfo;
  //               if (type === "timestamp" || type === "datetime") {
  //                 field = `DATE_FORMAT(${isSingle ? field : `${aName ?? tName}.${field}`}, '%Y-%m-%d %H:%i:%s') AS ${
  //                   isSingle || isMain ? field : `${tName}_${field}`
  //                 }`;
  //                 return field;
  //               }
  //               if (isSingle) return field;
  //               let subField = `${aName ?? tName}.${field}`;
  //               if (aliasName) subField += ` as ${aliasName}`;
  //               return subField;
  //             }
  //             if (t === "Array") {
  //               return fieldMod.join(" AS ");
  //             }
  //             // if(t==="Object"){
  //             // }
  //             throw new Error(`暂未处理此类型：${t}`);
  //           })
  //           // .filter(it => !!it.trim())
  //           .join(", ")
  //       );
  //     })
  //     .join(`,${N}`);
  //   let joinStr = "";
  //   if (!isSingle) {
  //     joinStr = getJoinTableStr(joinTableMap);
  //     if (joinStr) joinStr += N;
  //   }
  //   sql = `SELECT${nChar}${fieldsStr}${nChar}FROM ${mainName}${nChar}${joinStr}`;
  // }

  // WHERE
  const whereStr = getWhereStr(where, mainFieldNames, isSingle ? undefined : mainName, nChar);
  if (whereStr) sql += ` WHERE ${whereStr}`;

  // GROUP BY
  if (group?.length) {
    sql += ` GROUP BY ${group.join(",")}`;
  } else if (tableNames.length > 1) {
    sql += ` GROUP BY ${mainName}.id`;
  }

  // HAVING
  const havStr = getWhereStr(having, mainFieldNames, isSingle ? undefined : mainName, nChar);
  if (havStr) sql += ` HAVING ${havStr}`;

  // ORDER BY
  const orderStr = getOrderByStr(order, mainFieldNames);
  if (orderStr) sql += ` ORDER BY ${orderStr}`;
  if (limits?.length) sql += ` LIMIT ${limits.join(", ")}`;

  return `${sql};`;
};

/**
 * sql语句示例
 * @example SELECT user.user_name, order.id FROM user LEFT JOIN order ON user.id = order.user_id;
 */
